package org.cypress.rtpack.service.impl;

import org.cypress.rtpack.VO.SoeRawData;
import org.cypress.rtpack.VO.SoeViewData;
import org.cypress.rtpack.common.core.Result;
import org.cypress.rtpack.controller.SoeController;
import org.cypress.rtpack.service.ISoeService;
import org.cypress.rtpack.service.SoeDataConverter;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * @author getty
 */
@Service
public class SoeServiceImpl  implements ISoeService {
    private static final Logger logger = LoggerFactory.getLogger(SoeServiceImpl.class);

    /**
     * 构建同库联表查询SQL（两个表在同一数据库文件中）
     */
    private static String buildJoinQuery() {
        // 表名固定为 soe_ls 和 soe_pt（在同一数据库中）
        return "SELECT ls.unix_time1, ls.unix_time2, ls.channel_name, " +
                "ls.event_v, ls.val, pt.channel_alias, pt.tag_name, " +
                "pt.state0_alias, pt.state1_alias, pt.description " +
                "FROM soe_ls ls LEFT JOIN soe_pt pt " +
                "ON ls.channel_name = pt.channel_name";
    }

    @Override
    @Transactional(readOnly = true)  // 自动管理连接生命周期
    public List<SoeViewData> getSoeViewList(String sysnum) {
        String jdbcUrl = String.format("jdbc:sqlite:soe_s%spt.db",sysnum);  // 拼接JDBC URL

        // 使用 buildJoinQuery 获取SQL
        String sql = buildJoinQuery();

        try (Connection conn = DriverManager.getConnection(jdbcUrl);
             Statement stmt = conn.createStatement();


             ResultSet rs = stmt.executeQuery(sql)) {
            // 这里如何得到 List<SoeRawData>
            List<SoeRawData> resultList = new ArrayList<>();

            while (rs.next()) {
                SoeRawData data = new SoeRawData();
                // 映射基础字段
                data.setUnixTime1((int) rs.getLong("unix_time1"));
                data.setUnixTime2((int) rs.getLong("unix_time2"));
                data.setChannelName(rs.getString("channel_name"));
                data.setEventV(rs.getInt("event_v"));
                data.setVal(rs.getFloat("val"));

                // 映射可能为NULL的字段
                data.setChannelAlias(rs.getString("channel_alias"));
                data.setTagName(rs.getString("tag_name"));
                data.setState0Alias(rs.getString("state0_alias"));
                data.setState1Alias(rs.getString("state1_alias"));
                data.setDescription(rs.getString("description"));

                resultList.add(data);
            }
            logger.info("联合查询结果为{}",resultList);

            List<SoeViewData> viewDataList = SoeDataConverter.convert(resultList);
            return viewDataList;
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
        catch (Exception e){
            throw new RuntimeException("查询出错1101");
        }
    }
}
